<?php
	define('CONFIG_FILE',true);
	include '../config.php';
	
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';

	session_start();

	if(!empty($_SESSION["userType"])){
		$userType = $_SESSION["userType"];
	}
	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'Date';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'desc';
	$NRIC=$CustomerName='';
	if(isset($_POST['NRIC'])){
		$NRIC=$_POST['NRIC'];
	}
	if(isset($_POST['CustomerName'])){
		$CustomerName = $_POST['CustomerName'];
	}

    if(!empty($_POST['SearchDate'])){
		$Date = $_POST['SearchDate'];
	}else{
		$Date=date('Y-m-d', strtotime('first day of January last year'));
	}
	if(!empty($_POST['SearchDateTo'])){
		$DateTo = date('Y-m-d',strtotime($_POST['SearchDateTo']));
	}else{
		$DateTo=date('Y-m-d');
	}
	if(!empty($_POST['CentreLocation'])){
		$CentreLocation = $_POST['CentreLocation'];
	}else{
		$CentreLocation=0;
	}

	if(!empty($_POST['ActivityId'])){
		$ActivityId = $_POST['ActivityId'];
	}else{
		$ActivityId=0;
	}
	
	if($CentreLocation==0){
		$CentreLocation="";
	}
	if($ActivityId==0){
		$ActivityId="";
	}
	$offset = ($page-1)*$rows;
	$result = array();

	$sqlDBManager = new SqlDBManager();

	if($userType=="System Admin"){
		$sql = "SELECT count(*)
			FROM activityrecord ar, centre c, activity a ,client cl
			WHERE ar.ActivityId = a.ActivityId
			AND ar.CentreId = c.CentreId AND ar.NRIC=cl.NRIC
			AND (ar.NRIC LIKE  ?
			AND ar.Date BETWEEN ? AND ?
			AND ar.CentreId LIKE  ?
            AND cl.CustomerName LIKE ?
			AND ar.ActivityId LIKE  ?)";

		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);

		$CentreLocation=addslashes($CentreLocation);
		$CentreLocation=str_replace("%", "\%", $CentreLocation);
		$CentreLocation=str_replace("_", "\_", $CentreLocation);

		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);

		$ActivityId=addslashes($ActivityId);
		$ActivityId=str_replace("%", "\%", $ActivityId);
		$ActivityId=str_replace("_", "\_", $ActivityId);

		$parameters = array("%".$NRIC."%",$Date,$DateTo,"%".$CentreLocation."%","%".$CustomerName."%","%".$ActivityId."%");

		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
				
		$sql = "SELECT ar.ActivityRecordID, cl.CustomerName, a.Category, ar.NRIC, ar.Date, c.CentreName, a.ActivityName, ar.Performance,ar.Remarks
			FROM activityrecord ar, centre c, activity a,client cl
			WHERE ar.ActivityId = a.ActivityId
			AND ar.CentreId = c.CentreId AND ar.NRIC=cl.NRIC
			AND (ar.NRIC LIKE  ?
			AND ar.Date BETWEEN ? AND ?
			AND ar.CentreId LIKE  ?
            AND cl.CustomerName LIKE ?
			AND ar.ActivityId LIKE  ?)
			order by $sort $order limit $offset,$rows";
		
		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);

		$CentreLocation=addslashes($CentreLocation);
		$CentreLocation=str_replace("%", "\%", $CentreLocation);
		$CentreLocation=str_replace("_", "\_", $CentreLocation);

		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);

		$ActivityId=addslashes($ActivityId);
		$ActivityId=str_replace("%", "\%", $ActivityId);
		$ActivityId=str_replace("_", "\_", $ActivityId);

		$parameters = array("%".$NRIC."%",$Date,$DateTo,"%".$CentreLocation."%","%".$CustomerName."%","%".$ActivityId."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);

		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++){
				array_push($items, $res[$i]);
			}	
		}

		$sqlDBManager->close_connect();

		$result["rows"] = $items;
		echo json_encode($result);
	}else{
		if(!empty($_SESSION["centreName"])){
			$centreName = $_SESSION["centreName"];
		}
		$sql = "SELECT count(*)
			FROM activityrecord ar, centre c, activity a, client cl
			WHERE ar.ActivityId = a.ActivityId
			AND ar.CentreId = c.CentreId AND ar.NRIC=cl.NRIC
			AND (ar.NRIC LIKE  ?
			AND ar.Date BETWEEN ? AND ?
			AND cl.CustomerName LIKE ?
			AND ar.CentreId IN (Select CentreId from Centre where CentreName=?)
			AND ar.ActivityId LIKE  ?)";

		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);

		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);

		$ActivityId=addslashes($ActivityId);
		$ActivityId=str_replace("%", "\%", $ActivityId);
		$ActivityId=str_replace("_", "\_", $ActivityId);

		$parameters = array("%".$NRIC."%",$Date,$DateTo,"%".$CustomerName."%",$centreName,"%".$ActivityId."%");

		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
		
		$sql = "SELECT ar.ActivityRecordID,cl.CustomerName,a.Category, ar.NRIC, ar.Date, c.CentreName, a.ActivityName,ar.Performance,ar.Remarks
			FROM activityrecord ar, centre c, activity a, client cl
			WHERE ar.ActivityId = a.ActivityId
			AND ar.CentreId = c.CentreId AND ar.NRIC=cl.NRIC
			AND (ar.NRIC LIKE  ?
			AND ar.Date BETWEEN ? AND ?
			AND cl.CustomerName LIKE ?
			AND ar.CentreId IN (Select CentreId from Centre where CentreName=?)
			AND ar.ActivityId LIKE  ?)
			order by $sort $order limit $offset,$rows";
			
		$NRIC=addslashes($NRIC);
		$NRIC=str_replace("%", "\%", $NRIC);
		$NRIC=str_replace("_", "\_", $NRIC);

		$CustomerName=addslashes($CustomerName);
		$CustomerName=str_replace("%", "\%", $CustomerName);
		$CustomerName=str_replace("_", "\_", $CustomerName);

		$ActivityId=addslashes($ActivityId);
		$ActivityId=str_replace("%", "\%", $ActivityId);
		$ActivityId=str_replace("_", "\_", $ActivityId);

		$parameters = array("%".$NRIC."%",$Date,$DateTo,"%".$CustomerName."%",$centreName,"%".$ActivityId."%");
		
		$res=$sqlDBManager->queryRows($sql,$parameters);
		$items = array();
		if(!empty($res)){
			for($i=0;$i<count($res);$i++)
			{
				array_push($items, $res[$i]);
			}	
		}
		
		$sqlDBManager->close_connect();

		$result["rows"] = $items;
		echo json_encode($result);
	}
?>